package com.netcracker.util.dao.impl;

import com.netcracker.db.exception.DBException;
import com.netcracker.db.DBManager;
import com.netcracker.entity.record.GenreRecord;
import com.netcracker.util.dao.GenreDAO;
import com.netcracker.entity.Genre;
import com.netcracker.util.dao.DAOFacade;
import com.netcracker.util.dao.closer.DAOCloseHelper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GenreDAOImpl extends DAOCloseHelper implements GenreDAO {
    protected static final String GENRE_ID_VALUE = "GenreId";
    protected static final String GENRE_NAME_VALUE = "GenreName";
    protected static final String GENRE_PARENT_VALUE = "ParentGenre";

    protected static final String CREATE_GENRE_SQL = "INSERT INTO GENRES VALUES(GenreSequence.NEXTVAL,?,?)";
    protected static final String EDIT_GENRE_SQL = "UPDATE GENRES SET GenreName = ? WHERE GenreId = ?";
    protected static final String DELETE_GENRE_SQL = "DELETE FROM GENRES WHERE GenreId = ?";
    protected static final String FIND_ALL_GENRES = "SELECT * FROM GENRES";
    protected static final String FIND_GENRE_BY_ID = "SELECT * FROM GENRES WHERE GenreId = ?";
    protected static final String COUNT_GENRES_SQL = "SELECT COUNT(*) FROM GENRES";
    protected static final String FIND_BOOK_GENRES = "SELECT g.GenreId, g.GenreName, g.ParentGenre "
            + "FROM BOOKGENRE bg JOIN GENRES g ON bg.GenreId = g.GenreId WHERE bg.BookId = ?";
    protected static final String FIND_GENRE_BY_LOGIN_SQL = "SELECT * FROM GENRES WHERE GenreName = ?";

    private DBManager dbManager;
    private DAOFacade daoFacade;
    
    public GenreDAOImpl(DBManager dbManager, DAOFacade daoFacade) {
        this.dbManager = dbManager;
        this.daoFacade = daoFacade;
    }
        
    @Override
    public void setDAOFacade(DAOFacade daoFacade) {
        this.daoFacade = daoFacade;
    }
    
    @Override
    public void setDBManager(DBManager dbManager) {
        this.dbManager = dbManager;
    }
    
    @Override
    public void create(GenreRecord genre) throws DBException {
        PreparedStatement preparedStatement = null;
        try
        {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(CREATE_GENRE_SQL);
            preparedStatement.setString(1, genre.getName());
            preparedStatement.setInt(2, genre.getParentID());
            preparedStatement.executeUpdate();
            dbManager.commit();
        }
        catch (SQLException ex)
        {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public void edit(GenreRecord genre) throws DBException {
        PreparedStatement preparedStatement = null;
        try
        {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(EDIT_GENRE_SQL);
            preparedStatement.setString(1, genre.getName());
            preparedStatement.setInt(2, genre.getId());
            preparedStatement.executeUpdate();
            dbManager.commit();
        }catch(SQLException ex)
        {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public void remove(int genreID) throws DBException {
        PreparedStatement preparedStatement = null;
        try
        {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(DELETE_GENRE_SQL);
            preparedStatement.setInt(1, genreID);
            preparedStatement.executeUpdate();
            dbManager.commit();
        }catch(SQLException ex)
        {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public List<Genre> findAll() throws DBException {
        ResultSet resultSet = dbManager.execSQL(FIND_ALL_GENRES);
        List<Genre> genres = new ArrayList<>();
        try
        {
            while (resultSet.next())
            {
                genres.add(getGenreEntity(resultSet));
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally {
            closeResultSet(resultSet);
        }
        return genres;
    }

    @Override
    public int count() throws DBException {
        int count = 0;
        ResultSet resultSet = null;
        try
        {
            resultSet = dbManager.execSQL(COUNT_GENRES_SQL);
            while (resultSet.next())
            {
                count = resultSet.getInt(1);
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally {
            closeResultSet(resultSet);
        }
        return count;
    }

    @Override
    public List<Integer> findBookGenreIds(int bookId) throws DBException {
        PreparedStatement preparedStatement = null;
        List<Integer> genreIds = new ArrayList<>();
        ResultSet resultSet = null;
        try
        {
            preparedStatement = dbManager.prepareStatement(FIND_BOOK_GENRES);
            preparedStatement.setInt(1, bookId);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                genreIds.add(resultSet.getInt(GENRE_ID_VALUE));
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
        return genreIds;
    }

    @Override
    public Genre find(int id) throws DBException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Genre genre = null;
        try
        {
            preparedStatement = dbManager.prepareStatement(FIND_GENRE_BY_ID);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                genre = getGenreEntity(resultSet);
            }

        }catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
        return genre;
    }

    @Override
    public Genre find(String name) throws DBException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Genre genre = null;
        try
        {
            preparedStatement = dbManager.prepareStatement(FIND_GENRE_BY_LOGIN_SQL);
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                genre = getGenreEntity(resultSet);
            }

        }catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
        return genre;
    }

    @Override
    public List<Genre> findBookGenres(int bookID) throws DBException {
        PreparedStatement preparedStatement = null;
        List<Genre> genres = new ArrayList<>();
        ResultSet resultSet = null;
        try
        {
            preparedStatement = dbManager.prepareStatement(FIND_BOOK_GENRES);
            preparedStatement.setInt(1, bookID);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                genres.add(getGenreEntity(resultSet));
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
        return genres;
    }

    private Genre getGenreEntity(ResultSet resultSet) throws SQLException, DBException
    {
        Genre genre = new Genre();
        genre.setId(resultSet.getInt(GENRE_ID_VALUE));
        genre.setName(resultSet.getString(GENRE_NAME_VALUE));
        int parentId = resultSet.getInt(GENRE_PARENT_VALUE);
        if (parentId != 0)
            genre.setParentID(parentId);
        return genre;
    }
    
}
